import SimpleXMLRPCServer
import psycopg2
import sys
import xmlrpclib

import PyQRNative

class Server:
    def __init__(self): 
	self.con = None
	self.cur = None
	self._session_id_counter = 0
	self._trans_id_counter = 0
	self.server = xmlrpclib.Server('http://ec2-50-16-47-119.compute-1.amazonaws.com:8001')
    
#    def _privateFunction(self):
#        pass
#    
#    def ping(self):
#        return True
#    
#    def now(self):
#        return datetime.datetime.now()
#    
#    def show_type(self, arg):
#        return (str(arg), str(type(arg)), arg)

# needs to check pw
    def login(self, sessionid, username, pw):
	result = self._sql_select('users', 'user_name', username)
	if result == None:
		return dict(server = "valid", session = "valid", result = 'invalid')
	elif result[1] == pw:
		session_valid = "valid"
		self._sql_delete('sessionid', 'user_name', username)
		return dict(server = "valid", session = "valid", result = 'valid', sessionid = self._assign_session_id(username))
	return dict(server = "valid", session = "valid", result = 'invalid', sessionid = 'invalid')

# needs work
    def get_account(self, sessionid, username):
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		session_valid = "valid"	
		result = self._sql_select('users', 'user_name', username) 
		if result is not None:
			result_valid = "valid"
			return dict(server = "valid", session = session_valid, result = result_valid, data = result)
	return dict(server = "valid", session = session_valid, result = result_valid)

    def create_account(self, sessionid, username, pw, fname, lname):
	row_value = "'%s', '%s', '%s', '%s'" % (username, pw, fname, lname)
	result_valid = "invalid"
	result = self._sql_select('users', 'user_name', username) 
	if result is not None:
		return dict(server = "valid", session = "valid", result = result_valid, sessionid = self._assign_session_id(username))
	else:
		self._sql_insert('users', row_value)
		result_valid = "valid"
	return dict(server = "valid", session = "valid", result = result_valid, sessionid = self._assign_session_id(username))

    def update_account(self, sessionid, username, pw, fname, lname):
	row_value = "user_name = '%s', password = '%s', fname = '%s', lname = '%s'" % (username, pw, fname, lname)
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		self._sql_update('users', row_value, 'user_name', username)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid)

    def get_trans(self, sessionid, username):
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		session_valid = "valid"	
		result = self._sql_select('trans', 'seller_name', username)
		if result is not None:
			if result[2] != '':
				result_valid = "valid"
				return dict(server = "valid", session = session_valid, result = result_valid, data = result)
			else:
				self.delete_trans(sessionid, result[0])	
		else:
			dict(server = "valid", session = session_valid, result = result_valid)
	return dict(server = "valid", session = session_valid, result = result_valid)

    def create_trans(self, sessionid, username, productname, price, date):
	tid = str(self._trans_id_counter)
	self._trans_id_counter += 1
	row_value = "'%s', '%s', '%s', '%s', '%s', '%s'" % (tid, username, '', price, date, productname)
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		self._sql_insert('trans', row_value)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid)
    
    def update_trans_with_buyer(self, sessionid, tid, username):
	row_value = "buyer_name = '%s'" % (username)
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:		
		session_valid = "valid"	
		self._sql_update('trans', row_value, 'tid', tid)
		result = self._sql_select('trans', 'tid', tid)
		if result is None:
			return dict(server = "valid", session = session_valid, result = result_valid)
		result_valid = "valid"
	return dict(server = "valid", session = session_valid, result = result_valid, data = result)

    def delete_trans(self, sessionid, tid):
	return self._sql_delete('trans', 'tid', tid)

    def get_credit_card(self, sessionid, username, cardnumber):
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		result = self._sql_select('cc', 'card_number', cardnumber)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid, data = result)

    def get_credit_list(self, sessionid, username):
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		result = self._sql_select_all('cc', 'card_number','user_name', username)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid, data = result)
    
    def create_credit_card(self, sessionid, username, cardnumber, expdate, name, address1, address2, city, state, zipcode):
	row_value = "'%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'" % (username, cardnumber, expdate, name, address1, address2, city, state, zipcode)
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		self._sql_insert('cc', row_value)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid)

    def update_credit_card(self, sessionid, username, cardnumber, expdate, name, address1, address2, city, state, zipcode):
	row_value = "card_number = '%s', expdate = '%s', name = '%s', address1 = '%s', address2 = '%s', city = '%s', state = '%s', zipcode = '%s'" % (cardnumber, expdate, name, address1, address2, city, state, zipcode)
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		self._sql_update('cc', row_value, 'card_number' ,cardnumber)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid)

    def delete_credit_card(self, sessionid, username, cardnumber):
	session_valid = "invalid"
	result_valid = "invalid"
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		self._sql_delete('cc', 'card_number' ,cardnumber)
		result_valid = "valid"
		session_valid = "valid"	
	return dict(server = "valid", session = session_valid, result = result_valid)

    def generate_qr(self, username):
	qr = PyQRNative.QRCode(5, PyQRNative.QRErrorCorrectLevel.L)
	tid = self._sql_select('trans', 'seller_name', username)[0]
	qr.addData(tid)
	qr.make()

	im = qr.makeImage()
	im.save("server_image.png")
     	with open("server_image.png", "rb") as handle:
            return xmlrpclib.Binary(handle.read())

    def complete_trans(self, sessionid, username, cardnumber):
	session_valid = "invalid"
	result_valid = "invalid"
	price = 0
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		session_valid = "valid"
		result = self._sql_select('trans', 'buyer_name', username)
		if result is not None:
			self.server.add_trans(cardnumber, result[3])
			result_valid = "valid"
			#self.delete_trans(sessionid, result[0])	
			return dict(server = "valid", session = session_valid, result = result_valid, data = result)
	return dict(server = "valid", session = session_valid, result = result_valid)

    def sell_complete(self, sessionid, username):
	session_valid = "invalid"
	result_valid = "invalid"
	price = 0
	if sessionid == self._sql_select('sessionid', 'user_name', username)[1]:
		session_valid = "valid"
		result = self._sql_select('trans', 'seller_name', username, 'ORDER BY tid DESC')
		if result is not None:
			result_valid = "valid"
			self.delete_trans(sessionid, result[0])	
			return dict(server = "valid", session = session_valid, result = result_valid, data = result)
	return dict(server = "valid", session = session_valid, result = result_valid)

    def _sql_select(self, table, constraint, value, extra = ''):
        ver = None
        try:
            self._sql_connect()

            query = "SELECT * FROM %s WHERE %s = '%s' %s" % (table, constraint, value, extra)
            print query
	    self.cur.execute(query) 
            ver = self.cur.fetchone()
	    print ver
        except psycopg2.DatabaseError, e:
            print 'Error: %s' % e    
            sys.exit(1)
    
        finally:
            self._sql_disconnect()
	    return ver

    def _sql_select_all(self, table, projection,constraint, value):
        ver = None
        try:
            self._sql_connect()

            query = "SELECT %s FROM %s WHERE %s = '%s'" % (projection, table, constraint, value)
            print query
	    self.cur.execute(query) 
            ver = self.cur.fetchall()
	    print ver
	    return ver
        except psycopg2.DatabaseError, e:
            print 'Error: %s' % e    
            sys.exit(1)
    
        #finally:
            #return self._sql_disconnect()
    
    def _sql_insert(self, table, row):
	db_trans_complete = False
        try:
            self._sql_connect()

            query = "INSERT INTO %s VALUES (%s)" % (table, row)
	    print query
            self.cur.execute(query)
            # row standard format (i.e. 2,'Mercedes',57127)
            self.con.commit()
	    db_trans_complete = True
        except Exception as e:
            if self.con:
                self.con.rollback()

            print 'Error: %s' % e    
            sys.exit(1)
        
        finally:
            self._sql_disconnect()
	    return db_trans_complete
    
    def _sql_update(self, table, row, constraint, value):
	db_trans_complete = False
        try:
            self._sql_connect()
            
            query = "UPDATE %s SET %s WHERE %s = '%s'" % (table, row, constraint, value)
	    print query
            self.cur.execute(query)
            self.con.commit()
	    db_trans_complete = True
        except Exception as e:
            if self.con:
                self.con.rollback()
            
            print 'Error: %s' % e    
            sys.exit(1)
        
        finally:
            self._sql_disconnect()
	    return db_trans_complete

    def _sql_delete(self, table, constraint, value):
        db_trans_complete = False
        try:
            self._sql_connect()

            query = "DELETE FROM %s WHERE %s = '%s'" % (table, constraint, value)
	    print query
            self.cur.execute(query)
            self.con.commit()
	    db_trans_complete = True
        except Exception as e:
            if self.con:
                self.con.rollback()

            print 'Error: %s' % e    
            sys.exit(1)
        
        finally:
            self._sql_disconnect()
	    return db_trans_complete
        
    def _sql_connect(self):
        self.con = psycopg2.connect(database='cps316', user='postgres') 
	self.cur = self.con.cursor()
    
    def _sql_disconnect(self):
        if self.con:
            self.con.close()

    def _assign_session_id(self, user_name):
	session_id = str(self._session_id_counter)
	self._session_id_counter += 1
	row_value = "'%s', '%s'" % (user_name, session_id)
	self._sql_insert('sessionid', row_value)
	return session_id
	

# server running  
server = SimpleXMLRPCServer.SimpleXMLRPCServer(('ec2-50-16-47-119.compute-1.amazonaws.com', 8000), logRequests=True, allow_none=True)
server.register_introspection_functions()
server.register_multicall_functions()
server.register_instance(Server())
try:
    server.serve_forever()
except KeyboardInterrupt:
    print 'Exiting'

